import tkinter
import tkinter as tk
from tkinter import messagebox, SUNKEN, GROOVE, ttk
from tkinter import ttk
import pymysql
from ttkbootstrap import Style

import Login_Window


class student_gui(tk.Tk):
    def __init__(self, current_user):
        super().__init__()
        self.sno = current_user
        print(str(self.sno))
        self.img = tk.PhotoImage(file='用户.png')
        self.setup_UI()
        style = Style(theme='sandstone')
        style.configure('TLabel')
        style.configure('TButton')
        style.configure('TRadiobutton')
        style.configure('TPanedwindow')
        style.configure('Treeview')
        style.configure('TCombobox')
        self.title("学生窗体")
        self.geometry("600x550+342+123")
        self.resizable(0, 0)
        self.setup_UI()

    def setup_UI(self):
        conn = pymysql.connect(host='localhost', user='root', password='123456', database='数据库课程设计')
        cursor = conn.cursor()
        sql = 'select count(distinct 课程号) from SC where 学号 = %s' % (self.sno[0][0])
        cursor.execute(sql)
        result = cursor.fetchall()
        sql = 'select sum(学分) from SC where 学号 = %s' % (self.sno[0][0])
        cursor.execute(sql)
        result1 = cursor.fetchall()
        cursor.close()
        conn.close()
        ttk.Label(self,image=self.img).place(x=220,y=0)
        ttk.Label(self, style='TLabel',text="你一共选修了" + str(result[0][0]) + "门课程,获得" + str(result1[0][0]) + "个学分").place(x=300, y=20)

        ttk.Label(self,style='TLabel', text="欢迎你！学号:" + self.sno[0][0] + "  "
                            + self.sno[0][1] + "同学").place(x=300, y=0)
        mebubar = tk.Menu(self, relief=GROOVE)
        mebubar.add_command(label="学生选课", command=self.Course)
        mebubar.add_command(label="查看课程成绩", command=self.SC)
        mebubar.add_command(label="查看补考", command=self.View_result)
        mebubar.add_command(label="切换用户", command=self.cancellation)
        self.config(menu=mebubar)

    def Course(self):
        self.Pane_center = ttk.PanedWindow(width=600, height=550)
        ttk.Button(self.Pane_center, text='添加', command=self.add_couse,style='TButton').place(x=200, y=450, width=80, height=40)
        self.Tree = ttk.Treeview(self.Pane_center, columns=("sno", "names",
                                                            "credit", "teacher"),
                                 show="headings",
                                 height=20)
        # 设置每一个列的宽度和对齐的方式
        self.Tree.column("sno", width=120, anchor="center")
        self.Tree.column("names", width=120, anchor="center")
        self.Tree.column("credit", width=110, anchor="center")
        self.Tree.column("teacher", width=110, anchor="center")

        # 设置每个列的标题
        self.Tree.heading("sno", text="课程号")
        self.Tree.heading("names", text="课程名")
        self.Tree.heading("credit", text="学分")
        self.Tree.heading("teacher", text="任教老师")

        self.Tree.bind('<ButtonRelease-1>', self.treeviewClick)
        self.Tree.place(x=10, y=10)
        self.Pane_center.place(x=80, y=50)

        conn = pymysql.connect(host='localhost', user='root', password='123456', database='数据库课程设计')
        cursor = conn.cursor()
        sql = 'select * from course'
        cursor.execute(sql)
        result = cursor.fetchall()
        for index in range(len(result)):
            self.Tree.insert("", index, values=(result[index][0], result[index][1],
                                                result[index][2], result[index][3]))
        cursor.close()
        conn.close()

    def SC(self):
        conn = pymysql.connect(host='localhost', user='root', password='123456', database='数据库课程设计')
        cursor = conn.cursor()
        sql = 'select * from sc where 学号 = %s' % (self.sno[0][0])
        cursor.execute(sql)
        result = cursor.fetchall()
        self.SC_Pane_center = ttk.PanedWindow(width=600, height=550)
        # tk.Button(self.SC_Pane_center, text='添加').place(x=200, y=450, width=40, height=40)
        self.SC_Tree = tkinter.ttk.Treeview(self.SC_Pane_center, columns=("names",
                                                                          "teacher", "grade", "credit"),
                                            show="headings",
                                            height=20)
        self.SC_Tree.column("names", width=100, anchor="center")
        self.SC_Tree.column("teacher", width=100, anchor="center")
        self.SC_Tree.column("grade", width=100, anchor="center")
        self.SC_Tree.column("credit", width=100, anchor="center")

        self.SC_Tree.heading("names", text="课程号")
        self.SC_Tree.heading("teacher", text="任教老师")
        self.SC_Tree.heading("grade", text="成绩")
        self.SC_Tree.heading("credit", text="学分")

        for index in range(len(result)):
            self.SC_Tree.insert("", index, values=(result[index][1], result[index][2],
                                                   result[index][3], result[index][4]))
        self.SC_Tree.place(x=10, y=10)
        self.SC_Pane_center.place(x=80, y=50)
        cursor.close()
        conn.close()

    def View_result(self):
        conn = pymysql.connect(host='localhost', user='root', password='123456', database='数据库课程设计')
        cursor = conn.cursor()
        sql = 'select * from sc where 学号 = %s and 成绩<60' % (self.sno[0][0])
        cursor.execute(sql)
        result = cursor.fetchall()
        sql = 'select * from sc where 学号 = %s' % (self.sno[0][0])
        cursor.execute(sql)
        result1 = cursor.fetchall()
        sql = 'select 成绩 from sc where 学号 = %s' % (self.sno[0][0])
        cursor.execute(sql)
        result2 = cursor.fetchall()

        if result:

            self.V_Pane_center = ttk.PanedWindow(width=600, height=550)
            self.V_Tree = tkinter.ttk.Treeview(self.V_Pane_center, columns=("names",
                                                                            "teacher", "grade"),
                                               show="headings",
                                               height=20)
            self.V_Tree.column("names", width=120, anchor="center")
            self.V_Tree.column("teacher", width=120, anchor="center")
            self.V_Tree.column("grade", width=120, anchor="center")
            self.V_Tree.heading("names", text="课程号")
            self.V_Tree.heading("teacher", text="任教老师")
            self.V_Tree.heading("grade", text="成绩")

            for index in range(len(result)):
                self.V_Tree.insert("", index, values=(result[index][1], result[index][2],
                                                      result[index][3]))
            self.V_Tree.place(x=10, y=10)
            self.V_Pane_center.place(x=80, y=50)
            cursor.close()
            conn.close()
        elif result1:
            messagebox.showinfo(title='恭喜', message='你的课程都及格了！')
            return
        else:
            messagebox.showinfo(title='警告', message='你没有选修任何课程！')
            return

    def treeviewClick(self, event):
        for item in self.Tree.selection():
            self.item_text = self.Tree.item(item, "values")

    def add_couse(self):
        List = []
        print(self.item_text[0])
        conn = pymysql.connect(host='localhost', user='root', password='123456', database='数据库课程设计')
        cursor = conn.cursor()
        sql = 'select sum(学分) from sc where 学号= %s' % (self.sno[0][0])
        cursor.execute(sql)
        result1 = cursor.fetchall()
        if int(result1[0][0])>=20:
            messagebox.showinfo(title='警告', message='选修的学分不能超过20！')
            return
        sql = 'select sc.课程号 from sc where 学号 = %s ' % (self.sno[0][0])
        cursor.execute(sql)
        result = cursor.fetchall()
        for i in result:
            for a in i:
                List.append(a)
        if self.item_text[0] in List:
            messagebox.showinfo(title='警告', message='你已经选修该课程！')

            return
        else:
            sql = 'insert into sc(学号,课程号,任教老师) values (%s,%s,%s)'
            cursor.execute(sql, (str(self.sno[0][0]), str(self.item_text[0]), str(self.item_text[3])))
            conn.commit()
            cursor.close()
            conn.close()
            messagebox.showinfo(title='恭喜', message='选修该课程成功！')
    def cancellation(self):
        self.destroy()
        Login_Window.login_gui()
